﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace LeoK.Common
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class MySqlDB : IDisposable
    {
        private static string _connStr = "Server=55f5868b3a004.gz.cdb.myqcloud.com;Port=15756;Database=LeoK;User=cdb_outerroot;Password={0};charset=utf8;";
        private MySqlConnection _conn;
        private MySqlTransaction _trans;
        private bool _useTrans;

        public static void SetPWD(string pwd)
        {
            _connStr = string.Format(_connStr, pwd);
        }

        public MySqlDB(bool useTrans = false)
        {
            _useTrans = useTrans;
            _conn = new MySqlConnection(_connStr);
            _conn.Open();

            if (_useTrans)
            {
                _trans = _conn.BeginTransaction();
            }
        }

        public void Dispose()
        {
            if (_useTrans)
            {
                _trans.Dispose();
            }

            _conn.Close();
            _conn.Dispose();
        }

        public void BeginTranscation()
        {
            if (!_useTrans) throw new Exception("MDS:MySql没有启动事务");

            _trans = _conn.BeginTransaction();
        }

        public void Commit()
        {
            if (!_useTrans) throw new Exception("MDS:MySql没有启动事务");

            _trans.Commit();
        }

        public void Rollback()
        {
            if (!_useTrans) throw new Exception("MDS:MySql没有启动事务");

            _trans.Rollback();
        }

        #region 基本操作

        public async Task<object> ExecuteScalar(string sql, List<MySqlParameter> paras)
        {
            MySqlCommand cmd = _conn.CreateCommand();

            cmd.CommandText = sql;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras.ToArray());
            }
            
            return await cmd.ExecuteScalarAsync(); ;
        }

        public async Task<int> ExecuteScalarInt(string sql)
        {
            MySqlCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            object result = await cmd.ExecuteScalarAsync();

            if (result == DBNull.Value)
            {
                return 0;
            }

            return Int32.Parse(result.ToString());
        }

        public async Task<double> ExecuteScalarDouble(string sql)
        {
            MySqlCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            object result = await cmd.ExecuteScalarAsync();

            if (result == DBNull.Value)
            {
                return 0;
            }

            return Double.Parse(result.ToString());
        }

        public async Task<DbDataReader> ExecuteDataReader(string sql, List<MySqlParameter> paras)
        {
            MySqlCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras.ToArray());
            }

            DbDataReader result = await cmd.ExecuteReaderAsync();

            return result;
        }

        public async Task<int> ExecuteNonQuery(string sql, List<MySqlParameter> paras)
        {
            MySqlCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras.ToArray());
            } 

            int result = await cmd.ExecuteNonQueryAsync();

            return result;
        }

        public async Task<bool> IsExistRecord(string sql, List<MySqlParameter> paras)
        {
            object obj = await ExecuteScalar(sql, paras);

            return Int32.Parse(obj.ToString()) > 0;
        }

        public async Task<DataTable> ExecuteDataTable(string sql, List<MySqlParameter> paras)
        {
            DataTable result = new DataTable();

            MySqlCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras.ToArray());
            }

            MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
            dataAdapter.SelectCommand = cmd;
            await dataAdapter.FillAsync(result);

            return result;
        }

        #endregion

        #region 实体操作

        public async Task<T> QueryObj<T>(string sql, List<MySqlParameter> paras = null) where T : new()
        {
            DataTable table = await ExecuteDataTable(sql, paras);

            T result = default(T);
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (table.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            if (table.Rows.Count > 0)
            {
                DataRow row = table.Rows[0];
                result = new T();
                foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
                {
                    SetValue(result, pair.Value, row[pair.Key]);
                }
            }

            table.Dispose();

            return result;
        }

        public async Task<List<T>> QueryObjs<T>(string sql, List<MySqlParameter> paras = null) where T : new()
        {
            DataTable table = await ExecuteDataTable(sql, paras);

            List<T> result = new List<T>();
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (table.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            foreach (DataRow row in table.Rows)
            {
                T obj = new T();
                foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
                {
                    SetValue(obj, pair.Value, row[pair.Key]);
                }

                result.Add(obj);
            }

            table.Dispose();

            return result;
        }

        public async Task<int> InsertObj<T>(T obj)
        {
            string tableName = obj.GetType().Name;
            DataTable emptyTable = await ExecuteDataTable("SELECT * FROM " + tableName + " WHERE ID=-1", null);
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (emptyTable.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            string insertSql1 = "INSERT INTO " + tableName + " (";
            string insertSql2 = ") VALUES (";
            List<MySqlParameter> paras = new List<MySqlParameter>();
            foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
            {
                if (pair.Key != "ID")
                {
                    insertSql1 += pair.Key + ",";
                    insertSql2 += "@" + pair.Key + ",";

                    paras.Add(new MySqlParameter(pair.Key, GetValue(obj, pair.Value)));
                }
            }

            string insertSql = insertSql1.TrimEnd(',') + insertSql2.TrimEnd(',') + ");SELECT LAST_INSERT_ID();";
            object idObj = await ExecuteScalar(insertSql, paras);

            int id;
            if (!Int32.TryParse(idObj.ToString(), out id))
            {
                id = -1;
            }

            return id;
        }

        public async void InsertObjs<T>(List<T> objs)
        {
            if (objs.Count == 0) return;

            string tableName = objs[0].GetType().Name;
            DataTable emptyTable = await ExecuteDataTable("SELECT * FROM " + tableName + " WHERE ID=-1", null);
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (emptyTable.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            foreach (T obj in objs)
            {
                string insertSql1 = "INSERT INTO " + tableName + " (";
                string insertSql2 = ") VALUES (";
                List<MySqlParameter> paras = new List<MySqlParameter>();
                foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
                {
                    if (pair.Key != "ID")
                    {
                        insertSql1 += pair.Key + ",";
                        insertSql2 += "@" + pair.Key + ",";
                        paras.Add(new MySqlParameter(pair.Key, GetValue(obj, pair.Value)));
                    }
                }

                string insertSql = insertSql1.TrimEnd(',') + insertSql2.TrimEnd(',') + ");SELECT LAST_INSERT_ID();";
                string idStr = (await ExecuteScalar(insertSql, paras)).ToString();
                colMaps["ID"].SetValue(obj, Int32.Parse(idStr), null);
            }
        }

        public async void UpdateObjs<T>(List<T> objs)
        {
            if (objs.Count == 0) return;

            string tableName = objs[0].GetType().Name;
            DataTable emptyTable = await ExecuteDataTable("SELECT * FROM " + tableName + " WHERE ID=-1", null);
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (emptyTable.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            foreach (T obj in objs)
            {
                string insertSql1 = "UPDATE " + tableName + " SET ";
                List<MySqlParameter> paras = new List<MySqlParameter>();
                foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
                {
                    insertSql1 += pair.Key + "=@" + pair.Key + ",";
                    paras.Add(new MySqlParameter(pair.Key, GetValue(obj, pair.Value)));
                }

                string insertSql = insertSql1.TrimEnd(',') + " WHERE ID=@ID";
                await ExecuteScalar(insertSql, paras);
            }
        }

        public async void UpdateObj<T>(T obj)
        {
            string tableName = obj.GetType().Name;
            DataTable emptyTable = await ExecuteDataTable("SELECT * FROM " + tableName + " WHERE ID=-1", null);
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (emptyTable.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            string insertSql1 = "UPDATE " + tableName + " SET ";
            List<MySqlParameter> paras = new List<MySqlParameter>();
            foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
            {
                insertSql1 += pair.Key + "=@" + pair.Key + ",";
                paras.Add(new MySqlParameter(pair.Key, GetValue(obj, pair.Value)));
            }

            string insertSql = insertSql1.TrimEnd(',') + " WHERE ID=@ID";
            await ExecuteScalar(insertSql, paras);
        }

        public async void RevertObj<T>(T obj) where T : new()
        {
            string tableName = obj.GetType().Name;
            long id = (long)typeof(T).GetProperty("ID").GetValue(obj, null);
            DataTable table = await ExecuteDataTable("SELECT * FROM " + tableName + " WHERE ID=" + id, null);

            T result = default(T);
            Dictionary<string, PropertyInfo> colMaps = new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {
                if (table.Columns.Contains(propertyInfo.Name))
                {
                    colMaps[propertyInfo.Name] = propertyInfo;
                }
            }

            if (table.Rows.Count > 0)
            {
                DataRow row = table.Rows[0];
                result = new T();
                foreach (KeyValuePair<string, PropertyInfo> pair in colMaps)
                {
                    SetValue(result, pair.Value, row[pair.Key]);
                }
            }

            table.Dispose();
        }

        private object GetValue(object obj, PropertyInfo property)
        {
            object result = property.GetValue(obj, null);

            if (result == null)
            {
                return "";
            }
            if (result is bool)
            {
                return (bool)result ? 1 : 0;
            }
            if (property.PropertyType.IsEnum)
            {
                return (int)result;
            }

            return result;
        }

        private void SetValue(object obj, PropertyInfo property, object value)
        {
            if (property.PropertyType.Name == "Boolean")
            {
                if (value.ToString() == "1")
                {
                    property.SetValue(obj, true, null);
                }
                else
                {
                    property.SetValue(obj, false, null);
                }
            }
            else if (property.PropertyType.Name == "DateTime")
            {
                property.SetValue(obj, DateTime.Parse(value.ToString()), null);
            }
            else
            {
                property.SetValue(obj, value, null);
            }
        }

        #endregion
    }
}
